<!doctype html>



  


<html class="theme-next pisces use-motion" lang="zh-Hans">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>









<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />















  
  
  <link href="/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />




  
  
  
  

  
    
    
  

  

  

  

  

  
    
    
    <link href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic&subset=latin,latin-ext" rel="stylesheet" type="text/css">
  






<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css" />

<link href="/css/main.css?v=5.1.1" rel="stylesheet" type="text/css" />


  <meta name="keywords" content="数据库,mysql,转载," />








  <link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=5.1.1" />






<meta name="description" content="转载自    mysql-tutorial 开始使用MySQL 为关系型数据库(Relational Database Management System)，一个关系型数据库由一个或数个表格组成, 如图所示的一个表格：  表头(header): 每一列的名称; 列(col): 具有相同数据类型的数据的集合; 行(row): 每一行用来描述某个人/物的具体信息; 值(value): 行的具体信息,">
<meta name="keywords" content="数据库,mysql,转载">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL基础">
<meta property="og:url" content="http://yoursite.com/2017/07/20/MySQL基础/index.html">
<meta property="og:site_name" content="Blog by wclimb">
<meta property="og:description" content="转载自    mysql-tutorial 开始使用MySQL 为关系型数据库(Relational Database Management System)，一个关系型数据库由一个或数个表格组成, 如图所示的一个表格：  表头(header): 每一列的名称; 列(col): 具有相同数据类型的数据的集合; 行(row): 每一行用来描述某个人/物的具体信息; 值(value): 行的具体信息,">
<meta property="og:locale" content="zh-Hans">
<meta property="og:updated_time" content="2017-07-21T14:36:42.000Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="MySQL基础">
<meta name="twitter:description" content="转载自    mysql-tutorial 开始使用MySQL 为关系型数据库(Relational Database Management System)，一个关系型数据库由一个或数个表格组成, 如图所示的一个表格：  表头(header): 每一列的名称; 列(col): 具有相同数据类型的数据的集合; 行(row): 每一行用来描述某个人/物的具体信息; 值(value): 行的具体信息,">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Pisces',
    sidebar: {"position":"left","display":"post","offset":12,"offset_float":0,"b2t":false,"scrollpercent":false},
    fancybox: true,
    motion: true,
    duoshuo: {
      userId: '0',
      author: '博主'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="http://yoursite.com/2017/07/20/MySQL基础/"/>





  <title>MySQL基础 | Blog by wclimb</title>
  














</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail ">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/"  class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">Blog by wclimb</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle"></p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/categories/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-th"></i> <br />
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br />
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            标签
          </a>
        </li>
      
        
        <li class="menu-item menu-item-commonweal">
          <a href="/404.html" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-heartbeat"></i> <br />
            
            公益404
          </a>
        </li>
      

      
        <li class="menu-item menu-item-search">
          
            <a href="javascript:;" class="popup-trigger">
          
            
              <i class="menu-item-icon fa fa-search fa-fw"></i> <br />
            
            搜索
          </a>
        </li>
      
    </ul>
  

  
    <div class="site-search">
      
  <div class="popup search-popup local-search-popup">
  <div class="local-search-header clearfix">
    <span class="search-icon">
      <i class="fa fa-search"></i>
    </span>
    <span class="popup-btn-close">
      <i class="fa fa-times-circle"></i>
    </span>
    <div class="local-search-input-wrapper">
      <input autocomplete="off"
             placeholder="搜索..." spellcheck="false"
             type="text" id="local-search-input">
    </div>
  </div>
  <div id="local-search-result"></div>
</div>



    </div>
  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal " itemscope itemtype="http://schema.org/Article">
    <link itemprop="mainEntityOfPage" href="http://yoursite.com/2017/07/20/MySQL基础/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="wclimb">
      <meta itemprop="description" content="">
      <meta itemprop="image" content="/images/avatar.png">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Blog by wclimb">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">MySQL基础</h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2017-07-20T21:30:38+08:00">
                2017-07-20
              </time>
            

            

            
          </span>

          

          
            
          

          
          

          
            <span class="post-meta-divider">|</span>
            <span class="page-pv"><i class="fa fa-file-o"></i>   浏览
            <span class="busuanzi-value" id="busuanzi_value_page_pv" ></span>次
            </span>
          

          

          

        </div>
      </header>
    

    <div class="post-body" itemprop="articleBody">

      
      

      
        <p>转载自    <a href="https://github.com/jaywcjlove/mysql-tutorial" target="_blank" rel="external">mysql-tutorial</a></p>
<h3 id="开始使用"><a href="#开始使用" class="headerlink" title="开始使用"></a>开始使用</h3><p>MySQL 为关系型数据库(Relational Database Management System)，一个关系型数据库由一个或数个表格组成, 如图所示的一个表格：</p>
<ul>
<li><code>表头(header)</code>: 每一列的名称;</li>
<li><code>列(col)</code>: 具有相同数据类型的数据的集合;</li>
<li><code>行(row)</code>: 每一行用来描述某个人/物的具体信息;</li>
<li><code>值(value)</code>: 行的具体信息, 每个值必须与该列的数据类型相同;</li>
<li><code>键(key)</code>: 表中用来识别某个特定的人\物的方法, 键的值在当前列中具有唯一性。<h3 id="登录MySQL"><a href="#登录MySQL" class="headerlink" title="登录MySQL"></a>登录MySQL</h3></li>
</ul>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">mysql -h 127.0.0.1 -u 用户名 -p</div><div class="line">mysql -D 所选择的数据库名 -h 主机名 -u 用户名 -p</div><div class="line"><span class="meta">mysql&gt;</span> exit # 退出 使用 “quit;” 或 “\q;” 一样的效果</div><div class="line"><span class="meta">mysql&gt;</span> status;  # 显示当前mysql的version的各种信息</div><div class="line"><span class="meta">mysql&gt;</span> select version(); # 显示当前mysql的version信息</div><div class="line"><span class="meta">mysql&gt;</span> show global variables like 'port'; # 查看MySQL端口号</div></pre></td></tr></table></figure>
<h3 id="创建数据库"><a href="#创建数据库" class="headerlink" title="创建数据库"></a>创建数据库</h3><p>对于表的操作需要先进入库<code>use 库名;</code></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 创建一个名为 samp_db 的数据库，数据库字符编码指定为 gbk</span></div><div class="line"><span class="keyword">create</span> <span class="keyword">database</span> samp_db <span class="built_in">character</span> <span class="keyword">set</span> gbk;</div><div class="line"><span class="keyword">drop</span> <span class="keyword">database</span> samp_db; <span class="comment">-- 删除 库名为samp_db的库</span></div><div class="line"><span class="keyword">show</span> <span class="keyword">databases</span>;        <span class="comment">-- 显示数据库列表。</span></div><div class="line"><span class="keyword">use</span> samp_db;     <span class="comment">-- 选择创建的数据库samp_db</span></div><div class="line"><span class="keyword">show</span> <span class="keyword">tables</span>;     <span class="comment">-- 显示samp_db下面所有的表名字</span></div><div class="line"><span class="keyword">describe</span> 表名;    <span class="comment">-- 显示数据表的结构</span></div><div class="line"><span class="keyword">delete</span> <span class="keyword">from</span> 表名; <span class="comment">-- 清空表中记录</span></div></pre></td></tr></table></figure>
<a id="more"></a>
<h3 id="创建数据库表"><a href="#创建数据库表" class="headerlink" title="创建数据库表"></a>创建数据库表</h3><blockquote>
<p>使用 create table 语句可完成对表的创建, create table 的常见形式:<br>语法：create table 表名称(列声明);</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 如果数据库中存在user_accounts表，就把它从数据库中drop掉</span></div><div class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`user_accounts`</span>;</div><div class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`user_accounts`</span> (</div><div class="line">  <span class="string">`id`</span>             <span class="built_in">int</span>(<span class="number">100</span>) <span class="keyword">unsigned</span> <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT primary <span class="keyword">key</span>,</div><div class="line">  <span class="string">`password`</span>       <span class="built_in">varchar</span>(<span class="number">32</span>)       <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">''</span> <span class="keyword">COMMENT</span> <span class="string">'用户密码'</span>,</div><div class="line">  <span class="string">`reset_password`</span> tinyint(<span class="number">32</span>)       <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="number">0</span> <span class="keyword">COMMENT</span> <span class="string">'用户类型：0－不需要重置密码；1-需要重置密码'</span>,</div><div class="line">  <span class="string">`mobile`</span>         <span class="built_in">varchar</span>(<span class="number">20</span>)       <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">''</span> <span class="keyword">COMMENT</span> <span class="string">'手机'</span>,</div><div class="line">  <span class="string">`create_at`</span>      <span class="keyword">timestamp</span>(<span class="number">6</span>)      <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="keyword">CURRENT_TIMESTAMP</span>(<span class="number">6</span>),</div><div class="line">  <span class="string">`update_at`</span>      <span class="keyword">timestamp</span>(<span class="number">6</span>)      <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="keyword">CURRENT_TIMESTAMP</span>(<span class="number">6</span>) <span class="keyword">ON</span> <span class="keyword">UPDATE</span> <span class="keyword">CURRENT_TIMESTAMP</span>(<span class="number">6</span>),</div><div class="line">  <span class="comment">-- 创建唯一索引，不允许重复</span></div><div class="line">  <span class="keyword">UNIQUE</span> <span class="keyword">INDEX</span> idx_user_mobile(<span class="string">`mobile`</span>)</div><div class="line">)</div><div class="line"><span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8</div><div class="line"><span class="keyword">COMMENT</span>=<span class="string">'用户表信息'</span>;</div></pre></td></tr></table></figure>
<p>数据类型的属性解释</p>
<ul>
<li><code>NULL</code>：数据列可包含NULL值；</li>
<li><code>NOT NULL</code>：数据列不允许包含NULL值；</li>
<li><code>DEFAULT</code>：默认值；</li>
<li><code>PRIMARY</code>：KEY 主键；</li>
<li><code>AUTO_INCREMENT</code>：自动递增，适用于整数类型；</li>
<li><code>UNSIGNED</code>：是指数值类型只能为正数；</li>
<li><code>CHARACTER SET name</code>：指定一个字符集；</li>
<li><code>COMMENT</code>：对表或者字段说明；</li>
</ul>
<h3 id="增删改查"><a href="#增删改查" class="headerlink" title="增删改查"></a>增删改查</h3><h3 id="SELECT"><a href="#SELECT" class="headerlink" title="SELECT"></a>SELECT</h3><blockquote>
<p>SELECT 语句用于从表中选取数据。<br>语法：<code>SELECT 列名称 FROM 表名称</code><br>语法：<code>SELECT * FROM 表名称</code>  </p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 表station取个别名叫s，表station中不包含 字段id=13或者14 的，并且id不等于4的 查询出来，只显示id</span></div><div class="line"><span class="keyword">SELECT</span> s.id <span class="keyword">from</span> station s <span class="keyword">WHERE</span> <span class="keyword">id</span> <span class="keyword">in</span> (<span class="number">13</span>,<span class="number">14</span>) <span class="keyword">and</span> <span class="keyword">id</span> <span class="keyword">not</span> <span class="keyword">in</span> (<span class="number">4</span>);</div><div class="line"></div><div class="line"><span class="comment">-- 从表 Persons 选取 LastName 列的数据</span></div><div class="line"><span class="keyword">SELECT</span> LastName <span class="keyword">FROM</span> Persons</div><div class="line"></div><div class="line"><span class="comment">-- 从表 users 选取 id=3 的数据，并只拉一条数据(据说能优化性能)</span></div><div class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> <span class="keyword">users</span> <span class="keyword">where</span> <span class="keyword">id</span>=<span class="number">3</span> <span class="keyword">limit</span> <span class="number">1</span></div><div class="line"></div><div class="line"><span class="comment">-- 结果集中会自动去重复数据</span></div><div class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> Company <span class="keyword">FROM</span> Orders </div><div class="line"><span class="comment">-- 表 Persons 字段 Id_P 等于 Orders 字段 Id_P 的值，</span></div><div class="line"><span class="comment">-- 结果集显示 Persons表的 LastName、FirstName字段，Orders表的OrderNo字段</span></div><div class="line"><span class="keyword">SELECT</span> p.LastName, p.FirstName, o.OrderNo <span class="keyword">FROM</span> Persons p, Orders o <span class="keyword">WHERE</span> p.Id_P = o.Id_P </div><div class="line"></div><div class="line"><span class="comment">-- gbk 和 utf8 中英文混合排序最简单的办法 </span></div><div class="line"><span class="comment">-- ci是 case insensitive, 即 “大小写不敏感”</span></div><div class="line"><span class="keyword">SELECT</span> tag, <span class="keyword">COUNT</span>(tag) <span class="keyword">from</span> news <span class="keyword">GROUP</span> <span class="keyword">BY</span> tag <span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">convert</span>(tag <span class="keyword">using</span> gbk) <span class="keyword">collate</span> gbk_chinese_ci;</div><div class="line"><span class="keyword">SELECT</span> tag, <span class="keyword">COUNT</span>(tag) <span class="keyword">from</span> news <span class="keyword">GROUP</span> <span class="keyword">BY</span> tag <span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">convert</span>(tag <span class="keyword">using</span> utf8) <span class="keyword">collate</span> utf8_unicode_ci;</div></pre></td></tr></table></figure>
<h3 id="UPDATE"><a href="#UPDATE" class="headerlink" title="UPDATE"></a>UPDATE</h3><blockquote>
<p>Update 语句用于修改表中的数据。<br>语法：<code>UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值</code>  </p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- update语句设置字段值为另一个结果取出来的字段</span></div><div class="line"><span class="keyword">update</span> <span class="keyword">user</span> <span class="keyword">set</span> <span class="keyword">name</span> = (<span class="keyword">select</span> <span class="keyword">name</span> <span class="keyword">from</span> user1 <span class="keyword">where</span> user1 .id = <span class="number">1</span> )</div><div class="line"><span class="keyword">where</span> <span class="keyword">id</span> = (<span class="keyword">select</span> <span class="keyword">id</span> <span class="keyword">from</span> user2 <span class="keyword">where</span> user2 .name=<span class="string">'小苏'</span>);</div><div class="line"><span class="comment">-- 更新表 orders 中 id=1 的那一行数据更新它的 title 字段</span></div><div class="line"><span class="keyword">UPDATE</span> <span class="string">`orders`</span> <span class="keyword">set</span> title=<span class="string">'这里是标题'</span> <span class="keyword">WHERE</span> <span class="keyword">id</span>=<span class="number">1</span>;</div></pre></td></tr></table></figure>
<h3 id="INSERT"><a href="#INSERT" class="headerlink" title="INSERT"></a>INSERT</h3><blockquote>
<p>INSERT INTO 语句用于向表格中插入新的行。<br>语法：<code>INSERT INTO 表名称 VALUES (值1, 值2,....)</code><br>语法：<code>INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)</code>  </p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 向表 Persons 插入一条字段 LastName = JSLite 字段 Address = shanghai</span></div><div class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Persons (LastName, Address) <span class="keyword">VALUES</span> (<span class="string">'JSLite'</span>, <span class="string">'shanghai'</span>);</div><div class="line"><span class="comment">-- 向表 meeting 插入 字段 a=1 和字段 b=2</span></div><div class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> meeting <span class="keyword">SET</span> a=<span class="number">1</span>,b=<span class="number">2</span>;</div><div class="line"><span class="comment">-- </span></div><div class="line"><span class="comment">-- SQL实现将一个表的数据插入到另外一个表的代码</span></div><div class="line"><span class="comment">-- 如果只希望导入指定字段，可以用这种方法：</span></div><div class="line"><span class="comment">-- INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;</span></div><div class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> orders (user_account_id, title) <span class="keyword">SELECT</span> m.user_id, m.title <span class="keyword">FROM</span> meeting m <span class="keyword">where</span> m.id=<span class="number">1</span>;</div><div class="line"></div><div class="line"><span class="comment">-- 向表 charger 插入一条数据，已存在就对表 charger 更新 `type`,`update_at` 字段；</span></div><div class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`charger`</span> (<span class="string">`id`</span>,<span class="string">`type`</span>,<span class="string">`create_at`</span>,<span class="string">`update_at`</span>) <span class="keyword">VALUES</span> (<span class="number">3</span>,<span class="number">2</span>,<span class="string">'2017-05-18 11:06:17'</span>,<span class="string">'2017-05-18 11:06:17'</span>) <span class="keyword">ON</span> <span class="keyword">DUPLICATE</span> <span class="keyword">KEY</span> <span class="keyword">UPDATE</span> <span class="string">`id`</span>=<span class="keyword">VALUES</span>(<span class="string">`id`</span>), <span class="string">`type`</span>=<span class="keyword">VALUES</span>(<span class="string">`type`</span>), <span class="string">`update_at`</span>=<span class="keyword">VALUES</span>(<span class="string">`update_at`</span>);</div></pre></td></tr></table></figure>
<h3 id="DELETE"><a href="#DELETE" class="headerlink" title="DELETE"></a>DELETE</h3><blockquote>
<p>DELETE 语句用于删除表中的行。<br>语法：<code>DELETE FROM 表名称 WHERE 列名称 = 值</code>  </p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 在不删除table_name表的情况下删除所有的行，清空表。</span></div><div class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> table_name</div><div class="line"><span class="comment">-- 或者</span></div><div class="line"><span class="keyword">DELETE</span> * <span class="keyword">FROM</span> table_name</div><div class="line"><span class="comment">-- 删除 Person表字段 LastName = 'JSLite' </span></div><div class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> Person <span class="keyword">WHERE</span> LastName = <span class="string">'JSLite'</span> </div><div class="line"><span class="comment">-- 删除 表meeting id 为2和3的两条数据</span></div><div class="line"><span class="keyword">DELETE</span> <span class="keyword">from</span> meeting <span class="keyword">where</span> <span class="keyword">id</span> <span class="keyword">in</span> (<span class="number">2</span>,<span class="number">3</span>);</div></pre></td></tr></table></figure>
<h3 id="WHERE"><a href="#WHERE" class="headerlink" title="WHERE"></a>WHERE</h3><blockquote>
<p>WHERE 子句用于规定选择的标准。<br>语法：<code>SELECT 列名称 FROM 表名称 WHERE 列 运算符 值</code>  </p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 从表 Persons 中选出 Year 字段大于 1965 的数据</span></div><div class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> Persons <span class="keyword">WHERE</span> <span class="keyword">Year</span>&gt;<span class="number">1965</span></div></pre></td></tr></table></figure>
<h3 id="AND-和-OR"><a href="#AND-和-OR" class="headerlink" title="AND 和 OR"></a>AND 和 OR</h3><blockquote>
<p>AND - 如果第一个条件和第二个条件都成立；<br>OR - 如果第一个条件和第二个条件中只要有一个成立；  </p>
</blockquote>
<h3 id="AND"><a href="#AND" class="headerlink" title="AND"></a>AND</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 删除 meeting 表字段 </span></div><div class="line"><span class="comment">-- id=2 并且 user_id=5 的数据  和</span></div><div class="line"><span class="comment">-- id=3 并且 user_id=6 的数据 </span></div><div class="line"><span class="keyword">DELETE</span> <span class="keyword">from</span> meeting <span class="keyword">where</span> <span class="keyword">id</span> <span class="keyword">in</span> (<span class="number">2</span>,<span class="number">3</span>) <span class="keyword">and</span> user_id <span class="keyword">in</span> (<span class="number">5</span>,<span class="number">6</span>);</div><div class="line"></div><div class="line"><span class="comment">-- 使用 AND 来显示所有姓为 "Carter" 并且名为 "Thomas" 的人：</span></div><div class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> Persons <span class="keyword">WHERE</span> FirstName=<span class="string">'Thomas'</span> <span class="keyword">AND</span> LastName=<span class="string">'Carter'</span>;</div></pre></td></tr></table></figure>
<h3 id="OR"><a href="#OR" class="headerlink" title="OR"></a>OR</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 使用 OR 来显示所有姓为 "Carter" 或者名为 "Thomas" 的人：</span></div><div class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> Persons <span class="keyword">WHERE</span> firstname=<span class="string">'Thomas'</span> <span class="keyword">OR</span> lastname=<span class="string">'Carter'</span></div></pre></td></tr></table></figure>
<h3 id="ORDER-BY"><a href="#ORDER-BY" class="headerlink" title="ORDER BY"></a>ORDER BY</h3><blockquote>
<p>语句默认按照升序对记录进行排序。<br><code>ORDER BY</code> - 语句用于根据指定的列对结果集进行排序。<br><code>DESC</code> - 按照降序对记录进行排序。<br><code>ASC</code> - 按照顺序对记录进行排序。  </p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- Company在表Orders中为字母，则会以字母顺序显示公司名称</span></div><div class="line"><span class="keyword">SELECT</span> Company, OrderNumber <span class="keyword">FROM</span> Orders <span class="keyword">ORDER</span> <span class="keyword">BY</span> Company</div><div class="line"></div><div class="line"><span class="comment">-- 后面跟上 DESC 则为降序显示</span></div><div class="line"><span class="keyword">SELECT</span> Company, OrderNumber <span class="keyword">FROM</span> Orders <span class="keyword">ORDER</span> <span class="keyword">BY</span> Company <span class="keyword">DESC</span></div><div class="line"></div><div class="line"><span class="comment">-- Company以降序显示公司名称，并OrderNumber以顺序显示</span></div><div class="line"><span class="keyword">SELECT</span> Company, OrderNumber <span class="keyword">FROM</span> Orders <span class="keyword">ORDER</span> <span class="keyword">BY</span> Company <span class="keyword">DESC</span>, OrderNumber <span class="keyword">ASC</span></div></pre></td></tr></table></figure>
<h3 id="IN"><a href="#IN" class="headerlink" title="IN"></a>IN</h3><blockquote>
<p>IN - 操作符允许我们在 WHERE 子句中规定多个值。<br>IN - 操作符用来指定范围，范围中的每一条，都进行匹配。IN取值规律，由逗号分割，全部放置括号中。<br>语法：<code>SELECT &quot;字段名&quot;FROM &quot;表格名&quot;WHERE &quot;字段名&quot; IN (&#39;值一&#39;, &#39;值二&#39;, ...);</code></p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 从表 Persons 选取 字段 LastName 等于 Adams、Carter</span></div><div class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> Persons <span class="keyword">WHERE</span> LastName <span class="keyword">IN</span> (<span class="string">'Adams'</span>,<span class="string">'Carter'</span>)</div></pre></td></tr></table></figure>
<h3 id="NOT"><a href="#NOT" class="headerlink" title="NOT"></a>NOT</h3><blockquote>
<p>NOT - 操作符总是与其他操作符一起使用，用在要过滤的前面。</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="keyword">SELECT</span> vend_id, prod_name <span class="keyword">FROM</span> Products <span class="keyword">WHERE</span> <span class="keyword">NOT</span> vend_id = <span class="string">'DLL01'</span> <span class="keyword">ORDER</span> <span class="keyword">BY</span> prod_name;</div></pre></td></tr></table></figure>
<h3 id="UNION"><a href="#UNION" class="headerlink" title="UNION"></a>UNION</h3><blockquote>
<p>UNION - 操作符用于合并两个或多个 SELECT 语句的结果集。</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 列出所有在中国表（Employees_China）和美国（Employees_USA）的不同的雇员名</span></div><div class="line"><span class="keyword">SELECT</span> E_Name <span class="keyword">FROM</span> Employees_China <span class="keyword">UNION</span> <span class="keyword">SELECT</span> E_Name <span class="keyword">FROM</span> Employees_USA</div><div class="line"></div><div class="line"><span class="comment">-- 列出 meeting 表中的 pic_url，</span></div><div class="line"><span class="comment">-- station 表中的 number_station 别名设置成 pic_url 避免字段不一样报错</span></div><div class="line"><span class="comment">-- 按更新时间排序</span></div><div class="line"><span class="keyword">SELECT</span> <span class="keyword">id</span>,pic_url <span class="keyword">FROM</span> meeting <span class="keyword">UNION</span> ALL <span class="keyword">SELECT</span> <span class="keyword">id</span>,number_station <span class="keyword">AS</span> pic_url <span class="keyword">FROM</span> station  <span class="keyword">ORDER</span> <span class="keyword">BY</span> update_at;</div><div class="line"><span class="comment">-- 通过 UNION 语法同时查询了 products 表 和 comments 表的总记录数，并且按照 count 排序</span></div><div class="line"><span class="keyword">SELECT</span> <span class="string">'product'</span> <span class="keyword">AS</span> <span class="keyword">type</span>, <span class="keyword">count</span>(*) <span class="keyword">as</span> <span class="keyword">count</span> <span class="keyword">FROM</span> <span class="string">`products`</span> <span class="keyword">union</span> <span class="keyword">select</span> <span class="string">'comment'</span> <span class="keyword">as</span> <span class="keyword">type</span>, <span class="keyword">count</span>(*) <span class="keyword">as</span> <span class="keyword">count</span> <span class="keyword">FROM</span> <span class="string">`comments`</span> <span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">count</span>;</div></pre></td></tr></table></figure>
<h3 id="AS"><a href="#AS" class="headerlink" title="AS"></a>AS</h3><blockquote>
<p>as - 可理解为：用作、当成，作为；别名<br>一般是重命名列名或者表名。<br>语法：<code>select column_1 as 列1,column_2 as 列2 from table as 表</code></p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div></pre></td><td class="code"><pre><div class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> Employee <span class="keyword">AS</span> emp</div><div class="line"><span class="comment">-- 这句意思是查找所有Employee 表里面的数据，并把Employee表格命名为 emp。</span></div><div class="line"><span class="comment">-- 当你命名一个表之后，你可以在下面用 emp 代替 Employee.</span></div><div class="line"><span class="comment">-- 例如 SELECT * FROM emp.</span></div><div class="line"></div><div class="line"><span class="keyword">SELECT</span> <span class="keyword">MAX</span>(OrderPrice) <span class="keyword">AS</span> LargestOrderPrice <span class="keyword">FROM</span> Orders</div><div class="line"><span class="comment">-- 列出表 Orders 字段 OrderPrice 列最大值，</span></div><div class="line"><span class="comment">-- 结果集列不显示 OrderPrice 显示 LargestOrderPrice</span></div><div class="line"></div><div class="line"><span class="comment">-- 显示表 users_profile 中的 name 列</span></div><div class="line"><span class="keyword">SELECT</span> t.name <span class="keyword">from</span> (<span class="keyword">SELECT</span> * <span class="keyword">from</span> users_profile a) <span class="keyword">AS</span> t;</div><div class="line"></div><div class="line"><span class="comment">-- 表 user_accounts 命名别名 ua，表 users_profile 命名别名 up</span></div><div class="line"><span class="comment">-- 满足条件 表 user_accounts 字段 id 等于 表 users_profile 字段 user_id</span></div><div class="line"><span class="comment">-- 结果集只显示mobile、name两列</span></div><div class="line"><span class="keyword">SELECT</span> ua.mobile,up.name <span class="keyword">FROM</span> user_accounts <span class="keyword">as</span> ua <span class="keyword">INNER</span> <span class="keyword">JOIN</span> users_profile <span class="keyword">as</span> up <span class="keyword">ON</span> ua.id = up.user_id;</div></pre></td></tr></table></figure>
<h3 id="JOIN"><a href="#JOIN" class="headerlink" title="JOIN"></a>JOIN</h3><blockquote>
<p>用于根据两个或多个表中的列之间的关系，从这些表中查询数据。  </p>
</blockquote>
<ul>
<li><code>JOIN</code>: 如果表中有至少一个匹配，则返回行</li>
<li><code>INNER JOIN</code>:在表中存在至少一个匹配时，INNER JOIN 关键字返回行。</li>
<li><code>LEFT JOIN</code>: 即使右表中没有匹配，也从左表返回所有的行</li>
<li><code>RIGHT JOIN</code>: 即使左表中没有匹配，也从右表返回所有的行</li>
<li><code>FULL JOIN</code>: 只要其中一个表中存在匹配，就返回行</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line"><span class="keyword">SELECT</span> Persons.LastName, Persons.FirstName, Orders.OrderNo</div><div class="line"><span class="keyword">FROM</span> Persons</div><div class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> Orders</div><div class="line"><span class="keyword">ON</span> Persons.Id_P = Orders.Id_P</div><div class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> Persons.LastName;</div></pre></td></tr></table></figure>
<h3 id="SQL-函数"><a href="#SQL-函数" class="headerlink" title="SQL 函数"></a>SQL 函数</h3><h3 id="COUNT"><a href="#COUNT" class="headerlink" title="COUNT"></a>COUNT</h3><blockquote>
<p>COUNT 让我们能够数出在表格中有多少笔资料被选出来。<br>语法：<code>SELECT COUNT(&quot;字段名&quot;) FROM &quot;表格名&quot;;</code></p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 表 Store_Information 有几笔 store_name 栏不是空白的资料。</span></div><div class="line"><span class="comment">-- "IS NOT NULL" 是 "这个栏位不是空白" 的意思。</span></div><div class="line"><span class="keyword">SELECT</span> <span class="keyword">COUNT</span> (Store_Name) <span class="keyword">FROM</span> Store_Information <span class="keyword">WHERE</span> Store_Name <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="literal">NULL</span>; </div><div class="line"><span class="comment">-- 获取 Persons 表的总数</span></div><div class="line"><span class="keyword">SELECT</span> <span class="keyword">COUNT</span>(<span class="number">1</span>) <span class="keyword">AS</span> totals <span class="keyword">FROM</span> Persons;</div><div class="line"><span class="comment">-- 获取表 station 字段 user_id 相同的总数</span></div><div class="line"><span class="keyword">select</span> user_id, <span class="keyword">count</span>(*) <span class="keyword">as</span> totals <span class="keyword">from</span> station <span class="keyword">group</span> <span class="keyword">by</span> user_id;</div></pre></td></tr></table></figure>
<h3 id="MAX"><a href="#MAX" class="headerlink" title="MAX"></a>MAX</h3><blockquote>
<p>MAX 函数返回一列中的最大值。NULL 值不包括在计算中。<br>语法：<code>SELECT MAX(&quot;字段名&quot;) FROM &quot;表格名&quot;</code>  </p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 列出表 Orders 字段 OrderPrice 列最大值，</span></div><div class="line"><span class="comment">-- 结果集列不显示 OrderPrice 显示 LargestOrderPrice</span></div><div class="line"><span class="keyword">SELECT</span> <span class="keyword">MAX</span>(OrderPrice) <span class="keyword">AS</span> LargestOrderPrice <span class="keyword">FROM</span> Orders</div></pre></td></tr></table></figure>
<h3 id="触发器"><a href="#触发器" class="headerlink" title="触发器"></a>触发器</h3><blockquote>
<p>语法：<br>create trigger &lt;触发器名称&gt;<br>{ before | after}             # 之前或者之后出发<br>insert | update | delete      # 指明了激活触发程序的语句的类型<br>on &lt;表名&gt;                     # 操作哪张表<br>for each row                  # 触发器的执行间隔，for each row 通知触发器每隔一行执行一次动作，而不是对整个表执行一次。<br>&lt;触发器SQL语句&gt;</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line">delimiter $</div><div class="line"><span class="keyword">CREATE</span> <span class="keyword">TRIGGER</span> set_userdate <span class="keyword">BEFORE</span> <span class="keyword">INSERT</span> </div><div class="line"><span class="keyword">on</span> <span class="string">`message`</span></div><div class="line"><span class="keyword">for</span> <span class="keyword">EACH</span> <span class="keyword">ROW</span></div><div class="line"><span class="keyword">BEGIN</span></div><div class="line">  <span class="keyword">set</span> @statu = new.status; <span class="comment">-- 声明复制变量 statu</span></div><div class="line">  if @statu = 0 then       <span class="comment">-- 判断 statu 是否等于 0</span></div><div class="line">    <span class="keyword">UPDATE</span> <span class="string">`user_accounts`</span> <span class="keyword">SET</span> <span class="keyword">status</span>=<span class="number">1</span> <span class="keyword">WHERE</span> openid=NEW.openid;</div><div class="line">  <span class="keyword">end</span> <span class="keyword">if</span>;</div><div class="line"><span class="keyword">END</span></div><div class="line">$</div><div class="line">DELIMITER ; <span class="comment">-- 恢复结束符号</span></div></pre></td></tr></table></figure>
<p>OLD和NEW不区分大小写</p>
<ul>
<li>NEW 用NEW.col_name，没有旧行。在DELETE触发程序中，仅能使用OLD.col_name，没有新行。</li>
<li>OLD 用OLD.col_name来引用更新前的某一行的列</li>
</ul>
<h3 id="添加索引"><a href="#添加索引" class="headerlink" title="添加索引"></a>添加索引</h3><h3 id="普通索引-INDEX"><a href="#普通索引-INDEX" class="headerlink" title="普通索引(INDEX)"></a>普通索引(INDEX)</h3><blockquote>
<p>语法：ALTER TABLE <code>表名字</code> ADD INDEX 索引名字 ( <code>字段名字</code> )</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- –直接创建索引</span></div><div class="line"><span class="keyword">CREATE</span> <span class="keyword">INDEX</span> index_user <span class="keyword">ON</span> <span class="keyword">user</span>(title)</div><div class="line"><span class="comment">-- –修改表结构的方式添加索引</span></div><div class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> table_name <span class="keyword">ADD</span> <span class="keyword">INDEX</span> index_name <span class="keyword">ON</span> (<span class="keyword">column</span>(<span class="keyword">length</span>))</div><div class="line"><span class="comment">-- 给 user 表中的 name字段 添加普通索引(INDEX)</span></div><div class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="string">`table`</span> <span class="keyword">ADD</span> <span class="keyword">INDEX</span> index_name (<span class="keyword">name</span>)</div><div class="line"><span class="comment">-- –创建表的时候同时创建索引</span></div><div class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`table`</span> (</div><div class="line">    <span class="string">`id`</span> <span class="built_in">int</span>(<span class="number">11</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT ,</div><div class="line">    <span class="string">`title`</span> <span class="built_in">char</span>(<span class="number">255</span>) <span class="built_in">CHARACTER</span> <span class="keyword">SET</span> utf8 <span class="keyword">COLLATE</span> utf8_general_ci <span class="keyword">NOT</span> <span class="literal">NULL</span> ,</div><div class="line">    <span class="string">`content`</span> <span class="built_in">text</span> <span class="built_in">CHARACTER</span> <span class="keyword">SET</span> utf8 <span class="keyword">COLLATE</span> utf8_general_ci <span class="literal">NULL</span> ,</div><div class="line">    <span class="string">`time`</span> <span class="built_in">int</span>(<span class="number">10</span>) <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="literal">NULL</span> ,</div><div class="line">    PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>),</div><div class="line">    <span class="keyword">INDEX</span> index_name (title(<span class="keyword">length</span>))</div><div class="line">)</div><div class="line"><span class="comment">-- –删除索引</span></div><div class="line"><span class="keyword">DROP</span> <span class="keyword">INDEX</span> index_name <span class="keyword">ON</span> <span class="keyword">table</span></div></pre></td></tr></table></figure>
<h3 id="主键索引-PRIMARY-key"><a href="#主键索引-PRIMARY-key" class="headerlink" title="主键索引(PRIMARY key)"></a>主键索引(PRIMARY key)</h3><blockquote>
<p>语法：ALTER TABLE <code>表名字</code> ADD PRIMARY KEY ( <code>字段名字</code> )</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 给 user 表中的 id字段 添加主键索引(PRIMARY key)</span></div><div class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="string">`user`</span> <span class="keyword">ADD</span> PRIMARY <span class="keyword">key</span> (<span class="keyword">id</span>);</div></pre></td></tr></table></figure>
<h3 id="唯一索引-UNIQUE"><a href="#唯一索引-UNIQUE" class="headerlink" title="唯一索引(UNIQUE)"></a>唯一索引(UNIQUE)</h3><blockquote>
<p>语法：ALTER TABLE <code>表名字</code> ADD UNIQUE (<code>字段名字</code>)</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 给 user 表中的 creattime 字段添加唯一索引(UNIQUE)</span></div><div class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="string">`user`</span> <span class="keyword">ADD</span> <span class="keyword">UNIQUE</span> (creattime);</div></pre></td></tr></table></figure>
<h3 id="全文索引-FULLTEXT"><a href="#全文索引-FULLTEXT" class="headerlink" title="全文索引(FULLTEXT)"></a>全文索引(FULLTEXT)</h3><blockquote>
<p>语法：ALTER TABLE <code>表名字</code> ADD FULLTEXT (<code>字段名字</code>)</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 给 user 表中的 description 字段添加全文索引(FULLTEXT)</span></div><div class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="string">`user`</span> <span class="keyword">ADD</span> FULLTEXT (description);</div></pre></td></tr></table></figure>
<h3 id="添加多列索引"><a href="#添加多列索引" class="headerlink" title="添加多列索引"></a>添加多列索引</h3><blockquote>
<p>语法：<br>ALTER TABLE <code>table_name</code> ADD INDEX index_name ( <code>column1</code>, <code>column2</code>, <code>column3</code>)</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 给 user 表中的 name、city、age 字段添加名字为name_city_age的普通索引(INDEX)</span></div><div class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> <span class="keyword">user</span> <span class="keyword">ADD</span> <span class="keyword">INDEX</span> name_city_age (<span class="keyword">name</span>(<span class="number">10</span>),city,age);</div></pre></td></tr></table></figure>
<h3 id="建立索引的时机"><a href="#建立索引的时机" class="headerlink" title="建立索引的时机"></a>建立索引的时机</h3><p>在<code>WHERE</code>和<code>JOIN</code>中出现的列需要建立索引，但也不完全如此：</p>
<ul>
<li>MySQL只对<code>&lt;</code>，<code>&lt;=</code>，<code>=</code>，<code>&gt;</code>，<code>&gt;=</code>，<code>BETWEEN</code>，<code>IN</code>使用索引</li>
<li>某些时候的<code>LIKE</code>也会使用索引。</li>
<li>在<code>LIKE</code>以通配符%和_开头作查询时，MySQL不会使用索引。</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 此时就需要对city和age建立索引，</span></div><div class="line"><span class="comment">-- 由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。</span></div><div class="line"><span class="keyword">SELECT</span> t.Name  </div><div class="line"><span class="keyword">FROM</span> mytable t <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> mytable m <span class="keyword">ON</span> t.Name=m.username </div><div class="line"><span class="keyword">WHERE</span> m.age=<span class="number">20</span> <span class="keyword">AND</span> m.city=<span class="string">'上海'</span>;</div><div class="line"></div><div class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> mytable <span class="keyword">WHERE</span> username <span class="keyword">like</span><span class="string">'admin%'</span>; <span class="comment">-- 而下句就不会使用：</span></div><div class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> mytable WHEREt <span class="keyword">Name</span> <span class="keyword">like</span><span class="string">'%admin'</span>; <span class="comment">-- 因此，在使用LIKE时应注意以上的区别。</span></div></pre></td></tr></table></figure>
<p>索引的注意事项</p>
<ul>
<li>索引不会包含有NULL值的列</li>
<li>使用短索引</li>
<li>不要在列上进行运算 索引会失效</li>
</ul>
<h3 id="创建后表的修改"><a href="#创建后表的修改" class="headerlink" title="创建后表的修改"></a>创建后表的修改</h3><h3 id="添加列"><a href="#添加列" class="headerlink" title="添加列"></a>添加列</h3><blockquote>
<p>语法：<code>alter table 表名 add 列名 列数据类型 [after 插入位置];</code></p>
</blockquote>
<p>示例:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 在表students的最后追加列 address: </span></div><div class="line"><span class="keyword">alter</span> <span class="keyword">table</span> students <span class="keyword">add</span> address <span class="built_in">char</span>(<span class="number">60</span>);</div><div class="line"><span class="comment">-- 在名为 age 的列后插入列 birthday: </span></div><div class="line"><span class="keyword">alter</span> <span class="keyword">table</span> students <span class="keyword">add</span> birthday <span class="built_in">date</span> <span class="keyword">after</span> age;</div><div class="line"><span class="comment">-- 在名为 number_people 的列后插入列 weeks: </span></div><div class="line"><span class="keyword">alter</span> <span class="keyword">table</span> students <span class="keyword">add</span> <span class="keyword">column</span> <span class="string">`weeks`</span> <span class="built_in">varchar</span>(<span class="number">5</span>) <span class="keyword">not</span> <span class="literal">null</span> <span class="keyword">default</span> <span class="string">""</span> <span class="keyword">after</span> <span class="string">`number_people`</span>;</div></pre></td></tr></table></figure>
<h3 id="修改列"><a href="#修改列" class="headerlink" title="修改列"></a>修改列</h3><blockquote>
<p>语法：<code>alter table 表名 change 列名称 列新名称 新数据类型;</code></p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 将表 tel 列改名为 telphone: </span></div><div class="line"><span class="keyword">alter</span> <span class="keyword">table</span> students <span class="keyword">change</span> tel telphone <span class="built_in">char</span>(<span class="number">13</span>) <span class="keyword">default</span> <span class="string">"-"</span>;</div><div class="line"><span class="comment">-- 将 name 列的数据类型改为 char(16): </span></div><div class="line"><span class="keyword">alter</span> <span class="keyword">table</span> students <span class="keyword">change</span> <span class="keyword">name</span> <span class="keyword">name</span> <span class="built_in">char</span>(<span class="number">16</span>) <span class="keyword">not</span> <span class="literal">null</span>;</div><div class="line"><span class="comment">-- 修改 COMMENT 前面必须得有类型属性</span></div><div class="line"><span class="keyword">alter</span> <span class="keyword">table</span> students <span class="keyword">change</span> <span class="keyword">name</span> <span class="keyword">name</span> <span class="built_in">char</span>(<span class="number">16</span>) <span class="keyword">COMMENT</span> <span class="string">'这里是名字'</span>;</div><div class="line"><span class="comment">-- 修改列属性的时候 建议使用modify,不需要重建表</span></div><div class="line"><span class="comment">-- change用于修改列名字，这个需要重建表</span></div><div class="line"><span class="keyword">alter</span> <span class="keyword">table</span> meeting <span class="keyword">modify</span> <span class="string">`weeks`</span> <span class="built_in">varchar</span>(<span class="number">20</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">""</span> <span class="keyword">COMMENT</span> <span class="string">"开放日期 周一到周日：0~6，间隔用英文逗号隔开"</span>;</div></pre></td></tr></table></figure>
<h3 id="删除列"><a href="#删除列" class="headerlink" title="删除列"></a>删除列</h3><blockquote>
<p>语法：<code>alter table 表名 drop 列名称;</code></p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 删除表students中的 birthday 列: </span></div><div class="line"><span class="keyword">alter</span> <span class="keyword">table</span> students <span class="keyword">drop</span> birthday;</div></pre></td></tr></table></figure>
<h3 id="重命名表"><a href="#重命名表" class="headerlink" title="重命名表"></a>重命名表</h3><blockquote>
<p>语法：<code>alter table 表名 rename 新表名;</code></p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 重命名 students 表为 workmates: </span></div><div class="line"><span class="keyword">alter</span> <span class="keyword">table</span> students <span class="keyword">rename</span> workmates;</div></pre></td></tr></table></figure>
<h3 id="清空表数据"><a href="#清空表数据" class="headerlink" title="清空表数据"></a>清空表数据</h3><blockquote>
<p>方法一：<code>delete from 表名;</code><br>方法二：<code>truncate from &quot;表名&quot;;</code></p>
</blockquote>
<ul>
<li><code>DELETE:</code>1. DML语言;2. 可以回退;3. 可以有条件的删除;</li>
<li><code>TRUNCATE:</code>1. DDL语言;2. 无法回退;3. 默认所有的表内容都删除;4. 删除速度比delete快。</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 清空表为 workmates 里面的数据，不删除表。 </span></div><div class="line"><span class="keyword">delete</span> <span class="keyword">from</span> workmates;</div><div class="line"><span class="comment">-- 删除workmates表中的所有数据，且无法恢复</span></div><div class="line"><span class="keyword">truncate</span> <span class="keyword">from</span> workmates;</div></pre></td></tr></table></figure>
<h3 id="删除整张表"><a href="#删除整张表" class="headerlink" title="删除整张表"></a>删除整张表</h3><blockquote>
<p>语法：<code>drop table 表名;</code></p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 删除 workmates 表: </span></div><div class="line"><span class="keyword">drop</span> <span class="keyword">table</span> workmates;</div></pre></td></tr></table></figure>
<h3 id="删除整个数据库"><a href="#删除整个数据库" class="headerlink" title="删除整个数据库"></a>删除整个数据库</h3><blockquote>
<p>语法：<code>drop database 数据库名;</code></p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="comment">-- 删除 samp_db 数据库: </span></div><div class="line"><span class="keyword">drop</span> <span class="keyword">database</span> samp_db;</div></pre></td></tr></table></figure>

      
    </div>

    <div>
      
        

      
    </div>

    <div>
      
        

      
    </div>

    <div>
      
        

      
    </div>

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/tags/数据库/" rel="tag"># 数据库</a>
          
            <a href="/tags/mysql/" rel="tag"># mysql</a>
          
            <a href="/tags/转载/" rel="tag"># 转载</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/2017/07/18/读zepto源码，封装自己的zepto库-（三）/" rel="next" title="读zepto源码，封装自己的zepto库 （三）">
                <i class="fa fa-chevron-left"></i> 读zepto源码，封装自己的zepto库 （三）
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
          </div>
        </div>
      

      
      
    </footer>
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          


          
  <div class="comments" id="comments">
    
  </div>


        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap" >
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
          <img class="site-author-image" itemprop="image"
               src="/images/avatar.png"
               alt="wclimb" />
          <p class="site-author-name" itemprop="name">wclimb</p>
           
              <p class="site-description motion-element" itemprop="description"></p>
          
        </div>
        <nav class="site-state motion-element">

          
            <div class="site-state-item site-state-posts">
              <a href="/archives/">
                <span class="site-state-item-count">7</span>
                <span class="site-state-item-name">日志</span>
              </a>
            </div>
          

          
            
            
            <div class="site-state-item site-state-categories">
              <a href="/categories/index.html">
                <span class="site-state-item-count">4</span>
                <span class="site-state-item-name">分类</span>
              </a>
            </div>
          

          
            
            
            <div class="site-state-item site-state-tags">
              <a href="/tags/index.html">
                <span class="site-state-item-count">11</span>
                <span class="site-state-item-name">标签</span>
              </a>
            </div>
          

        </nav>

        

        <div class="links-of-author motion-element">
          
            
              <span class="links-of-author-item">
                <a href="https://github.com/wclimb" target="_blank" title="GitHub">
                  
                    <i class="fa fa-fw fa-github"></i>
                  
                  GitHub
                </a>
              </span>
            
              <span class="links-of-author-item">
                <a href="http://weibo.com/u/3966368179" target="_blank" title="Weibo">
                  
                    <i class="fa fa-fw fa-weibo"></i>
                  
                  Weibo
                </a>
              </span>
            
          
        </div>

        
        

        
        
          <div class="links-of-blogroll motion-element links-of-blogroll-inline">
            <div class="links-of-blogroll-title">
              <i class="fa  fa-fw fa-globe"></i>
              Links
            </div>
            <ul class="links-of-blogroll-list">
              
                <li class="links-of-blogroll-item">
                  <a href="http://www.lukedever.cn" title="Lukedever" target="_blank">Lukedever</a>
                </li>
              
            </ul>
          </div>
        

        


      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-3"><a class="nav-link" href="#开始使用"><span class="nav-number">1.</span> <span class="nav-text">开始使用</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#登录MySQL"><span class="nav-number">2.</span> <span class="nav-text">登录MySQL</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#创建数据库"><span class="nav-number">3.</span> <span class="nav-text">创建数据库</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#创建数据库表"><span class="nav-number">4.</span> <span class="nav-text">创建数据库表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#增删改查"><span class="nav-number">5.</span> <span class="nav-text">增删改查</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#SELECT"><span class="nav-number">6.</span> <span class="nav-text">SELECT</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#UPDATE"><span class="nav-number">7.</span> <span class="nav-text">UPDATE</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#INSERT"><span class="nav-number">8.</span> <span class="nav-text">INSERT</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#DELETE"><span class="nav-number">9.</span> <span class="nav-text">DELETE</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#WHERE"><span class="nav-number">10.</span> <span class="nav-text">WHERE</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#AND-和-OR"><span class="nav-number">11.</span> <span class="nav-text">AND 和 OR</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#AND"><span class="nav-number">12.</span> <span class="nav-text">AND</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#OR"><span class="nav-number">13.</span> <span class="nav-text">OR</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#ORDER-BY"><span class="nav-number">14.</span> <span class="nav-text">ORDER BY</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#IN"><span class="nav-number">15.</span> <span class="nav-text">IN</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#NOT"><span class="nav-number">16.</span> <span class="nav-text">NOT</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#UNION"><span class="nav-number">17.</span> <span class="nav-text">UNION</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#AS"><span class="nav-number">18.</span> <span class="nav-text">AS</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#JOIN"><span class="nav-number">19.</span> <span class="nav-text">JOIN</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#SQL-函数"><span class="nav-number">20.</span> <span class="nav-text">SQL 函数</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#COUNT"><span class="nav-number">21.</span> <span class="nav-text">COUNT</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#MAX"><span class="nav-number">22.</span> <span class="nav-text">MAX</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#触发器"><span class="nav-number">23.</span> <span class="nav-text">触发器</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#添加索引"><span class="nav-number">24.</span> <span class="nav-text">添加索引</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#普通索引-INDEX"><span class="nav-number">25.</span> <span class="nav-text">普通索引(INDEX)</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#主键索引-PRIMARY-key"><span class="nav-number">26.</span> <span class="nav-text">主键索引(PRIMARY key)</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#唯一索引-UNIQUE"><span class="nav-number">27.</span> <span class="nav-text">唯一索引(UNIQUE)</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#全文索引-FULLTEXT"><span class="nav-number">28.</span> <span class="nav-text">全文索引(FULLTEXT)</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#添加多列索引"><span class="nav-number">29.</span> <span class="nav-text">添加多列索引</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#建立索引的时机"><span class="nav-number">30.</span> <span class="nav-text">建立索引的时机</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#创建后表的修改"><span class="nav-number">31.</span> <span class="nav-text">创建后表的修改</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#添加列"><span class="nav-number">32.</span> <span class="nav-text">添加列</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#修改列"><span class="nav-number">33.</span> <span class="nav-text">修改列</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#删除列"><span class="nav-number">34.</span> <span class="nav-text">删除列</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#重命名表"><span class="nav-number">35.</span> <span class="nav-text">重命名表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#清空表数据"><span class="nav-number">36.</span> <span class="nav-text">清空表数据</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#删除整张表"><span class="nav-number">37.</span> <span class="nav-text">删除整张表</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#删除整个数据库"><span class="nav-number">38.</span> <span class="nav-text">删除整个数据库</span></a></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright" >
  
  &copy; 
  <span itemprop="copyrightYear">2017</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">wclimb</span>
</div>


<div class="powered-by">
  由 <a class="theme-link" href="https://hexo.io">Hexo</a> 强力驱动
</div>

<div class="theme-info">
  主题 -
  <a class="theme-link" href="https://github.com/iissnan/hexo-theme-next">
    NexT.Pisces
  </a>
</div>

<span id="showDays"></span>
<script>
var birthDay = new Date("6/27/2017");
var now = new Date();
var duration = now.getTime() - birthDay.getTime();
var total= Math.floor(duration / (1000 * 60 * 60 * 24));
document.getElementById("showDays").innerHTML = "本站已运行 "+total+" 天";
</script>


        
<div class="busuanzi-count">
  <script async src="https://dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js"></script>

  
    <span class="site-uv">
      <i class="fa fa-user"></i>  访问人数
      <span class="busuanzi-value" id="busuanzi_value_site_uv"></span>
      人次
    </span>
  

  
</div>


        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  


  











  
  <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>

  
  <script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>

  
  <script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>

  
  <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>

  
  <script type="text/javascript" src="/lib/canvas-nest/canvas-nest.min.js"></script>


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.1.1"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.1.1"></script>



  
  


  <script type="text/javascript" src="/js/src/affix.js?v=5.1.1"></script>

  <script type="text/javascript" src="/js/src/schemes/pisces.js?v=5.1.1"></script>



  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.1"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=5.1.1"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.1"></script>



  


  




	





  





  





  






  

  <script type="text/javascript">
    // Popup Window;
    var isfetched = false;
    var isXml = true;
    // Search DB path;
    var search_path = "search.xml";
    if (search_path.length === 0) {
      search_path = "search.xml";
    } else if (/json$/i.test(search_path)) {
      isXml = false;
    }
    var path = "/" + search_path;
    // monitor main search box;

    var onPopupClose = function (e) {
      $('.popup').hide();
      $('#local-search-input').val('');
      $('.search-result-list').remove();
      $('#no-result').remove();
      $(".local-search-pop-overlay").remove();
      $('body').css('overflow', '');
    }

    function proceedsearch() {
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay"></div>')
        .css('overflow', 'hidden');
      $('.search-popup-overlay').click(onPopupClose);
      $('.popup').toggle();
      var $localSearchInput = $('#local-search-input');
      $localSearchInput.attr("autocapitalize", "none");
      $localSearchInput.attr("autocorrect", "off");
      $localSearchInput.focus();
    }

    // search function;
    var searchFunc = function(path, search_id, content_id) {
      'use strict';

      // start loading animation
      $("body")
        .append('<div class="search-popup-overlay local-search-pop-overlay">' +
          '<div id="search-loading-icon">' +
          '<i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>' +
          '</div>' +
          '</div>')
        .css('overflow', 'hidden');
      $("#search-loading-icon").css('margin', '20% auto 0 auto').css('text-align', 'center');

      $.ajax({
        url: path,
        dataType: isXml ? "xml" : "json",
        async: true,
        success: function(res) {
          // get the contents from search data
          isfetched = true;
          $('.popup').detach().appendTo('.header-inner');
          var datas = isXml ? $("entry", res).map(function() {
            return {
              title: $("title", this).text(),
              content: $("content",this).text(),
              url: $("url" , this).text()
            };
          }).get() : res;
          var input = document.getElementById(search_id);
          var resultContent = document.getElementById(content_id);
          var inputEventFunction = function() {
            var searchText = input.value.trim().toLowerCase();
            var keywords = searchText.split(/[\s\-]+/);
            if (keywords.length > 1) {
              keywords.push(searchText);
            }
            var resultItems = [];
            if (searchText.length > 0) {
              // perform local searching
              datas.forEach(function(data) {
                var isMatch = false;
                var hitCount = 0;
                var searchTextCount = 0;
                var title = data.title.trim();
                var titleInLowerCase = title.toLowerCase();
                var content = data.content.trim().replace(/<[^>]+>/g,"");
                var contentInLowerCase = content.toLowerCase();
                var articleUrl = decodeURIComponent(data.url);
                var indexOfTitle = [];
                var indexOfContent = [];
                // only match articles with not empty titles
                if(title != '') {
                  keywords.forEach(function(keyword) {
                    function getIndexByWord(word, text, caseSensitive) {
                      var wordLen = word.length;
                      if (wordLen === 0) {
                        return [];
                      }
                      var startPosition = 0, position = [], index = [];
                      if (!caseSensitive) {
                        text = text.toLowerCase();
                        word = word.toLowerCase();
                      }
                      while ((position = text.indexOf(word, startPosition)) > -1) {
                        index.push({position: position, word: word});
                        startPosition = position + wordLen;
                      }
                      return index;
                    }

                    indexOfTitle = indexOfTitle.concat(getIndexByWord(keyword, titleInLowerCase, false));
                    indexOfContent = indexOfContent.concat(getIndexByWord(keyword, contentInLowerCase, false));
                  });
                  if (indexOfTitle.length > 0 || indexOfContent.length > 0) {
                    isMatch = true;
                    hitCount = indexOfTitle.length + indexOfContent.length;
                  }
                }

                // show search results

                if (isMatch) {
                  // sort index by position of keyword

                  [indexOfTitle, indexOfContent].forEach(function (index) {
                    index.sort(function (itemLeft, itemRight) {
                      if (itemRight.position !== itemLeft.position) {
                        return itemRight.position - itemLeft.position;
                      } else {
                        return itemLeft.word.length - itemRight.word.length;
                      }
                    });
                  });

                  // merge hits into slices

                  function mergeIntoSlice(text, start, end, index) {
                    var item = index[index.length - 1];
                    var position = item.position;
                    var word = item.word;
                    var hits = [];
                    var searchTextCountInSlice = 0;
                    while (position + word.length <= end && index.length != 0) {
                      if (word === searchText) {
                        searchTextCountInSlice++;
                      }
                      hits.push({position: position, length: word.length});
                      var wordEnd = position + word.length;

                      // move to next position of hit

                      index.pop();
                      while (index.length != 0) {
                        item = index[index.length - 1];
                        position = item.position;
                        word = item.word;
                        if (wordEnd > position) {
                          index.pop();
                        } else {
                          break;
                        }
                      }
                    }
                    searchTextCount += searchTextCountInSlice;
                    return {
                      hits: hits,
                      start: start,
                      end: end,
                      searchTextCount: searchTextCountInSlice
                    };
                  }

                  var slicesOfTitle = [];
                  if (indexOfTitle.length != 0) {
                    slicesOfTitle.push(mergeIntoSlice(title, 0, title.length, indexOfTitle));
                  }

                  var slicesOfContent = [];
                  while (indexOfContent.length != 0) {
                    var item = indexOfContent[indexOfContent.length - 1];
                    var position = item.position;
                    var word = item.word;
                    // cut out 100 characters
                    var start = position - 20;
                    var end = position + 80;
                    if(start < 0){
                      start = 0;
                    }
                    if (end < position + word.length) {
                      end = position + word.length;
                    }
                    if(end > content.length){
                      end = content.length;
                    }
                    slicesOfContent.push(mergeIntoSlice(content, start, end, indexOfContent));
                  }

                  // sort slices in content by search text's count and hits' count

                  slicesOfContent.sort(function (sliceLeft, sliceRight) {
                    if (sliceLeft.searchTextCount !== sliceRight.searchTextCount) {
                      return sliceRight.searchTextCount - sliceLeft.searchTextCount;
                    } else if (sliceLeft.hits.length !== sliceRight.hits.length) {
                      return sliceRight.hits.length - sliceLeft.hits.length;
                    } else {
                      return sliceLeft.start - sliceRight.start;
                    }
                  });

                  // select top N slices in content

                  var upperBound = parseInt('1');
                  if (upperBound >= 0) {
                    slicesOfContent = slicesOfContent.slice(0, upperBound);
                  }

                  // highlight title and content

                  function highlightKeyword(text, slice) {
                    var result = '';
                    var prevEnd = slice.start;
                    slice.hits.forEach(function (hit) {
                      result += text.substring(prevEnd, hit.position);
                      var end = hit.position + hit.length;
                      result += '<b class="search-keyword">' + text.substring(hit.position, end) + '</b>';
                      prevEnd = end;
                    });
                    result += text.substring(prevEnd, slice.end);
                    return result;
                  }

                  var resultItem = '';

                  if (slicesOfTitle.length != 0) {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + highlightKeyword(title, slicesOfTitle[0]) + "</a>";
                  } else {
                    resultItem += "<li><a href='" + articleUrl + "' class='search-result-title'>" + title + "</a>";
                  }

                  slicesOfContent.forEach(function (slice) {
                    resultItem += "<a href='" + articleUrl + "'>" +
                      "<p class=\"search-result\">" + highlightKeyword(content, slice) +
                      "...</p>" + "</a>";
                  });

                  resultItem += "</li>";
                  resultItems.push({
                    item: resultItem,
                    searchTextCount: searchTextCount,
                    hitCount: hitCount,
                    id: resultItems.length
                  });
                }
              })
            };
            if (keywords.length === 1 && keywords[0] === "") {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-search fa-5x" /></div>'
            } else if (resultItems.length === 0) {
              resultContent.innerHTML = '<div id="no-result"><i class="fa fa-frown-o fa-5x" /></div>'
            } else {
              resultItems.sort(function (resultLeft, resultRight) {
                if (resultLeft.searchTextCount !== resultRight.searchTextCount) {
                  return resultRight.searchTextCount - resultLeft.searchTextCount;
                } else if (resultLeft.hitCount !== resultRight.hitCount) {
                  return resultRight.hitCount - resultLeft.hitCount;
                } else {
                  return resultRight.id - resultLeft.id;
                }
              });
              var searchResultList = '<ul class=\"search-result-list\">';
              resultItems.forEach(function (result) {
                searchResultList += result.item;
              })
              searchResultList += "</ul>";
              resultContent.innerHTML = searchResultList;
            }
          }

          if ('auto' === 'auto') {
            input.addEventListener('input', inputEventFunction);
          } else {
            $('.search-icon').click(inputEventFunction);
            input.addEventListener('keypress', function (event) {
              if (event.keyCode === 13) {
                inputEventFunction();
              }
            });
          }

          // remove loading animation
          $(".local-search-pop-overlay").remove();
          $('body').css('overflow', '');

          proceedsearch();
        }
      });
    }

    // handle and trigger popup window;
    $('.popup-trigger').click(function(e) {
      e.stopPropagation();
      if (isfetched === false) {
        searchFunc(path, 'local-search-input', 'local-search-result');
      } else {
        proceedsearch();
      };
    });

    $('.popup-btn-close').click(onPopupClose);
    $('.popup').click(function(e){
      e.stopPropagation();
    });
    $(document).on('keyup', function (event) {
      var shouldDismissSearchPopup = event.which === 27 &&
        $('.search-popup').is(':visible');
      if (shouldDismissSearchPopup) {
        onPopupClose();
      }
    });
  </script>





  

  

  

  

  

  

</body>
</html>
